package workforce.limousine.struts;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.DynaActionForm;

import limousine.util.SelectItem;
import limousine.vo.Users;
import limousine.vo.Users;
import limousine.util.Service;
import limousine.util.Token;
import limousine.util.Func;
import limousine.util.PageIterator;
import limousine.util.PageIteratorFactory;

public class UsersManagerQuery extends Action {

    private String order = "";
    private Func func = new Func();

    public ActionForward execute(ActionMapping mapping, ActionForm form,
            HttpServletRequest request, HttpServletResponse response)
            throws Exception {
        HttpSession session = request.getSession();
        System.out.println("***UsersManagerQuery");

        Users user = (Users) session.getAttribute(Token.USER_SESSION);
        if (user == null) {
            System.out.println("--SESSION EXPIRE--");
            return mapping.findForward(Token.SESSION_EXPIRE);
        }
        /*if(!user.getRole_name().equals("Admin")){
        System.out.println("--Not Admin Hack--");
        return mapping.findForward(Token.SESSION_EXPIRE);
        }*/

        session.removeAttribute(Token.USERS);
        session.removeAttribute(Token.USERS_ROLE_LIST);
        session.removeAttribute(Token.USERS_GENDER_LIST);
        session.removeAttribute(Token.USERS_ENABLE_LIST);
        session.removeAttribute(Token.USERS_STATUS_LIST);
        session.removeAttribute(Token.USERS_PAGE);
        session.removeAttribute(Token.USERS_CURRENT_OFFSET);

        //        DataSource ds = this.getDataSource(request, Token.DATASOURCE_NAME);
        Service service = new Service();
        service.setServletContext(this.getServlet().getServletContext());
        DataSource ds = service.getDataSource();
        service.removeMsgAttribute(request, session, "");

        DynaActionForm f = (DynaActionForm) form;

        String limit = (request.getParameter("limit") == null) ? "15" : request.getParameter("limit");
        String offset = (request.getParameter("offset") == null) ? "0" : request.getParameter("offset");
        session.setAttribute(Token.USERS_CURRENT_OFFSET,offset);

        String userName = func.protectNullString(f.getString("userName"));
        String name = func.protectNullString(f.getString("name"));
        String gender = func.protectNullString(f.getString("gender"));
        String email = func.protectNullString(f.getString("email"));
        String enable = func.protectNullString(f.getString("enable"));
        String status = func.protectNullString(f.getString("status"));
        String roleId = func.protectNullString(f.getString("roleId"));


        f.set("userName", userName);
        f.set("name", name);
        f.set("email", email);        

        //protect '
        userName = userName.replaceAll("'","\\\\'");
        name = name.replaceAll("'","\\\\'");
        email = email.replaceAll("'","\\\\'");


        order = request.getParameter("order");
        order = "order by " + ((order == null) ? "user_name" : order);



        List<Users> list = new ArrayList<Users>();

        StringBuffer sql = new StringBuffer();
        int i=0;
        if(userName.length() > 0){
            sql.append(service.getCondition(i)+"user_name like '%"+userName.trim()+"%' ");
            i++;
        }
        if(name.length() > 0){
            sql.append(service.getCondition(i)+"name like '%"+name.trim()+"%' ");
            i++;
        }
        if(email.length() > 0){
            sql.append(service.getCondition(i)+"email like '%"+email.trim()+"%' ");
            i++;
        }
        if(gender.length() > 0){
            sql.append(service.getCondition(i)+"gender = '"+gender.trim()+"' ");
            i++;
        }
        if(enable.length() > 0){
            sql.append(service.getCondition(i)+"enable ='"+enable.trim()+"' ");
            i++;
        }
        if(status.length() > 0){
            sql.append(service.getCondition(i)+"status ='"+status.trim()+"' ");
            i++;
        }
        if(roleId.length() > 0){
            sql.append(service.getCondition(i)+"role_id ='"+roleId.trim()+"' ");
            i++;
        }else{
            sql.append(service.getCondition(i)+"role_id > '"+user.getRoleId().trim()+"' ");
            i++;
        }

        StringBuffer sqlCount = new StringBuffer();
        sqlCount.append("select count(*) from users " + sql.toString());

        sql.append(order);
        sql.append(" limit "+limit+" offset "+offset);
        System.out.println("SQL: "+sql.toString());
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("Select *,(select name from role where id=role_id ) as ROLE_NAME From users "+sql.toString());
            while (rs.next()) {
                Users o = new Users();
                o.setId(rs.getString("ID"));
                o.setUserName(rs.getString("USER_NAME"));
                o.setPassword(rs.getString("PASSWORD"));
                o.setName(rs.getString("NAME"));
                o.setEmail(rs.getString("EMAIL"));
                o.setPhone(rs.getString("PHONE"));
                o.setGender(rs.getString("GENDER"));
                o.setCreateDate(rs.getString("CREATE_DATE"));
                o.setLastLogin(rs.getString("LAST_LOGIN"));
                o.setEnable(rs.getString("ENABLE"));
                o.setRoleId(rs.getString("ROLE_ID"));
                o.setStatus(rs.getString("STATUS"));
                o.setRoleName(rs.getString("ROLE_NAME"));
                list.add(o);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        System.out.println("size " + list.size());
        if (list.size() > 0) {
            session.setAttribute(Token.USERS, list);
        }

        int total = getCount(ds, sqlCount.toString());
        PageIteratorFactory pf = new PageIteratorFactory();
        List<PageIterator> pageList = pf.buildPages(total, Integer.parseInt(limit), "usersmanagerquery.do");
        session.setAttribute(Token.USERS_PAGE,pageList);


        List<SelectItem> roleList = service.getRoleListByPrivilege(ds,user.getRoleId(),roleId,true);
        if (roleList.size() > 0) {
            session.setAttribute(Token.USERS_ROLE_LIST, roleList);
        }

        List<SelectItem> enableList = service.getEnableList(enable, true);
        if (enableList.size() > 0) {
            session.setAttribute(Token.USERS_ENABLE_LIST, enableList);
        }

        List<SelectItem> statusList = service.getStatusList(status, true);
        if (statusList.size() > 0) {
            session.setAttribute(Token.USERS_STATUS_LIST, statusList);
        }

        List<SelectItem> genderList = service.getGenderList(gender, true);
        if (genderList.size() > 0) {
            session.setAttribute(Token.USERS_GENDER_LIST, genderList);
        }

        //service.updateControlPanel(session, ds, user.getRoleId());//update control panel display

        return mapping.findForward(Token.SUCCESS);
    }

    public List<Users> getUser(DataSource ds) {
        StringBuffer sql = new StringBuffer();
        sql.append("Select *,(select name from role where id=role_id) as ROLE_NAME From users order by user_name");
        List<Users> l = new ArrayList<Users>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            while (rs.next()) {
                Users o = new Users();
                o.setId(rs.getString("ID"));
                o.setUserName(rs.getString("USER_NAME"));
                o.setPassword(rs.getString("PASSWORD"));
                o.setName(rs.getString("NAME"));
                o.setEmail(rs.getString("EMAIL"));
                o.setPhone(rs.getString("PHONE"));
                o.setGender(rs.getString("GENDER"));
                o.setCreateDate(rs.getString("CREATE_DATE"));
                o.setLastLogin(rs.getString("LAST_LOGIN"));
                o.setEnable(rs.getString("ENABLE"));
                o.setRoleId(rs.getString("ROLE_ID"));
                o.setStatus(rs.getString("STATUS"));
                o.setRoleName(rs.getString("ROLE_NAME"));
                l.add(o);
            }
            rs.close();
            stmt.close();
            conn.close();
            return l;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    public int getCount(DataSource ds, String sql) {
        int total = 0;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next()) {
                total = rs.getInt(1);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return total;
    }
}
